package universal.schema.model;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;

import com.opensymphony.xwork2.ActionContext;

import universal.schema.util.DB;

public class SearchModel {

	static ArrayList<String> queryResult= new ArrayList<String>();
	String sqlQuery = "SELECT ";

	public ArrayList<String> getQueryResult() {
		return queryResult;
	}

	public void setQueryResult(ArrayList<String> queryResult) {
		SearchModel.queryResult = queryResult;
	}



	public String singleTableQueryResults( String[] colList, String tableName,ArrayList<Filters> where) {
		queryResult.clear();
		System.out.println("inside singleTable Query result..before session creation");
		System.out.println("values..."+colList[0]);
		int colsCount = colList.length;
		String where_clause=" WHERE ";
		/* making the select query*/


		//making columns to be displayed 
		for(int i=0;i<colsCount;i++){
			System.out.println("i value="+i);
			sqlQuery=sqlQuery.concat(colList[i]);
			if(i != (colsCount-1))
				sqlQuery=sqlQuery.concat(", ");
		}//end for

		//appending the table name
		sqlQuery=sqlQuery.concat(" FROM "+ tableName + " ");	

		if(where!=null && where.size() > 0)	
		{
			for(int i=0;i<where.size();i++)
			{
				where_clause=where_clause.concat(where.get(i).getColumns()+" ");
				
				//if filter operators are for string data type 
				if(where.get(i).getOperators().equalsIgnoreCase("equals"))
				{
					where_clause+="='"+ where.get(i).getFiltervalue()+"'";
					
				}
				else if(where.get(i).getOperators().equalsIgnoreCase("contains"))
				{
					where_clause+="like '%"+ where.get(i).getFiltervalue()+"%' ";
				}
				else if(where.get(i).getOperators().equalsIgnoreCase("starts-with"))
				{
					where_clause+="like '"+ where.get(i).getFiltervalue()+"%' ";
				}
				else //other data types filters
				{
					where_clause=where_clause.concat(where.get(i).getOperators()+" ");
					where_clause=where_clause.concat(where.get(i).getFiltervalue()+" ");
				}
				
				
				if(i != (where.size()-1))
				{
					where_clause=where_clause.concat(where.get(i).getConnectors()+" ");
				}

			} //end for
			where_clause=where_clause.concat(";");
			sqlQuery = sqlQuery.concat(where_clause);
		} //end if
		
		else
		{
			sqlQuery=sqlQuery.concat(";");
		}


		System.out.println("QUERY>....."+sqlQuery);


		return sqlQuery;

	}


	public String singleTableQueryResultsNoWhere( String[] colList, String tableName) {
		queryResult.clear();
		int colsCount = colList.length;

		/* making the select query*/


		//making columns to be displayed 
		for(int i=0;i<colsCount;i++){
			System.out.println("i value="+i);
			sqlQuery=sqlQuery.concat(colList[i]);
			if(i != (colsCount-1))
				sqlQuery=sqlQuery.concat(", ");
		}

		//appending the table name
		sqlQuery=sqlQuery.concat(" FROM "+ tableName + " ;");	

		System.out.println("no where query"+sqlQuery);

		return sqlQuery;

	}



//fire the SQl qUERY
	public ArrayList<String> results()
	{
		ResultSet resultSet = null;
		String finalCols[];
		try{
			Connection connection = DB.getConnection();
			String query = (String)ActionContext.getContext().getSession().get("queryBefore");
	
			resultSet = DB.readFromDB(query, connection);
			if(resultSet == null)
				return null;
			finalCols=(String[])ActionContext.getContext().getSession().get("selectedColumns"); 
	
			while(resultSet.next()){
	
				for(int j = 0; j <finalCols.length; j++)
				{
					String type=getDataType(finalCols[j]);
					if(!type.isEmpty())
					{
						if(type.equalsIgnoreCase("varchar") || type.equalsIgnoreCase("char") || type.equalsIgnoreCase("text")||type.equalsIgnoreCase("longtext")||type.equalsIgnoreCase("mediumtext"))
							queryResult.add(resultSet.getString(finalCols[j]));
						else if(type.equalsIgnoreCase("bool"))
							queryResult.add(resultSet.getBoolean(finalCols[j])+""); //do for boolean
						else if(type.equalsIgnoreCase("int") || type.equalsIgnoreCase("tinyint") ||type.equalsIgnoreCase("smallint"))
							queryResult.add(resultSet.getInt(finalCols[j])+""); //do for boolean
						else if(type.equalsIgnoreCase("double") || type.equalsIgnoreCase("decimal"))
							queryResult.add(resultSet.getDouble(finalCols[j])+"");
						else if( type.equalsIgnoreCase("bigint"))
							queryResult.add(resultSet.getLong(finalCols[j])+"");
						else if(type.equalsIgnoreCase("float"))
							queryResult.add(resultSet.getFloat(finalCols[j])+"");
						else if(type.equalsIgnoreCase("date"))
							queryResult.add(resultSet.getDate(finalCols[j])+"");
					}

				}//end for

			} //end while
			return queryResult;
		}

		catch(Exception e)
		{
			return null;
		}

	}

	public String getDataType(String colName)
	{
		ArrayList<Column> list=(ArrayList<Column>)ActionContext.getContext().getSession().get("Colsdatatypes");
		for(int i=0;i<list.size();i++)
		{
			if(list.get(i).getColName().equalsIgnoreCase(colName))
				return list.get(i).getColType();
		}

		return "";
	}

	public String getSqlQuery() {
		return sqlQuery;
	}



	public void setSqlQuery(String sqlQuery) {
		this.sqlQuery = sqlQuery;
	}



	public String multipleTableResults(String[] finalCols, String[] finalTables, ArrayList<Filters> where) {
		// TODO Auto-generated method stub

		ArrayList<Node> path=(ArrayList<Node>)ActionContext.getContext().getSession().get("finalJoinPath");

		if(path!=null){ //this is for isConnected condition join
			
			String displayCols="";
			String where_clause="";
			
			ArrayList<Node> selectedTablesNodes=(ArrayList<Node>)ActionContext.getContext().getSession().get("selectedNodesList"); // join_keys	
			
			displayCols=generateDisplayCols(selectedTablesNodes,finalCols);
			
			displayCols=displayCols.substring(0, displayCols.length()-2);
			
			sqlQuery+=displayCols + " FROM ";
			for(int i=0;i<path.size();i++)
			{
				if(i<path.size()-1)
					sqlQuery+=path.get(i).getTblObj().getName()+", ";
				else
					sqlQuery+=path.get(i).getTblObj().getName();
			}
			
			sqlQuery+=" WHERE ";

			String joins="";

			for(int i=0;i<path.size();i++)
			{
				//appending table name
				if(i<path.size()-1)
				{
					joins+=path.get(i).getTblObj().getName()+".";
					joins+=path.get(i+1).getForeignKey()+" = ";   //form A.a = 
					joins+=path.get(i+1).getTblObj().getName()+".";
					joins+=path.get(i+1).getPrimaryKey() + " AND ";  //forms A.a = B.a
				}
			}
			joins=joins.substring(0, joins.length()-5);

			sqlQuery+=" "+joins+" AND ";



			//Adding Filters
			if(where!=null && where.size() > 0)	
			{
				for(int i=0;i<where.size();i++)
				{
					where_clause=where_clause.concat(where.get(i).getColumns()+" ");
					
					//if filter operators are for string data type 
					if(where.get(i).getOperators().equalsIgnoreCase("equals"))
					{
						where_clause+="= '" + where.get(i).getFiltervalue() + "' ";
						
					}
					else if(where.get(i).getOperators().equalsIgnoreCase("contains"))
					{
						where_clause+="like '%"+ where.get(i).getFiltervalue()+"%' ";
					}
					else if(where.get(i).getOperators().equalsIgnoreCase("starts-with"))
					{
						where_clause+="like '"+ where.get(i).getFiltervalue()+"%' ";
					}
					else //other data types filters
					{
						where_clause=where_clause.concat(where.get(i).getOperators()+" ");
						where_clause=where_clause.concat(where.get(i).getFiltervalue()+" ");
					}
					
					
					if(i != (where.size()-1))
					{
						where_clause=where_clause.concat(where.get(i).getConnectors()+" ");
					}

				} //end for
				where_clause=where_clause.concat(";");
				sqlQuery = sqlQuery.concat(where_clause);
			} //end if

		}
		else{ //this join is for isAssociation
			
			
			ArrayList<Node> pathAssociation=(ArrayList<Node>)ActionContext.getContext().getSession().get("finalJoinPathAssociation");
			String displayCols="";
			String where_clause="";
			ArrayList<Node> selectedTablesNodes=(ArrayList<Node>)ActionContext.getContext().getSession().get("selectedNodesList");	
			displayCols=generateDisplayCols(selectedTablesNodes,finalCols);

			displayCols=displayCols.substring(0, displayCols.length()-2);

			sqlQuery+=displayCols + " FROM ";
			for(int i=0;i<pathAssociation.size();i++)
			{
				if(i<pathAssociation.size()-1)
					sqlQuery+=pathAssociation.get(i).getTblObj().getName()+", ";
				else
					sqlQuery+=pathAssociation.get(i).getTblObj().getName();
			}
			sqlQuery+=" WHERE ";
			System.out.println("query till from clause "+sqlQuery);
			String joins="";
			
			//we have to join threee tables alwayz so 2 join conditions will be there
			//Intermediate node(enrollment) does not have primary or foreign key at all
			//first join first and middle table i.e enrollment and course(for example)
			joins+=pathAssociation.get(0).getTblObj().getName()+".";
			joins+=pathAssociation.get(0).getForeignKey()+" = ";   //form A.a = 
			joins+=pathAssociation.get(1).getTblObj().getName()+"."; // association class
			joins+=pathAssociation.get(0).getPrimaryKey() + " AND ";  //forms A.a = B.a

			//then join middle table and last one: enrollment and student(for example)
			joins+=pathAssociation.get(pathAssociation.size()-1).getTblObj().getName()+".";
			joins+=pathAssociation.get(pathAssociation.size()-1).getForeignKey()+" = ";   //form A.a = 
			joins+=pathAssociation.get(1).getTblObj().getName()+"."; // association class
			joins+=pathAssociation.get(pathAssociation.size()-1).getPrimaryKey();  //forms A.a = B.a

			sqlQuery+=" "+joins+" AND ";

			//Adding Filters

			if(where!=null && where.size() > 0)	
			{
				for(int i=0;i<where.size();i++)
				{
					where_clause=where_clause.concat(where.get(i).getColumns()+" ");
					
					//if filter operators are for string data type 
					if(where.get(i).getOperators().equalsIgnoreCase("equals"))
					{
						where_clause+="= '" + where.get(i).getFiltervalue() + "' ";
					}
					else if(where.get(i).getOperators().equalsIgnoreCase("contains"))
					{
						where_clause+="like '%"+ where.get(i).getFiltervalue()+"%' ";
					}
					else if(where.get(i).getOperators().equalsIgnoreCase("starts-with"))
					{
						where_clause+="like '"+ where.get(i).getFiltervalue()+"%' ";
					}
					else //other data types filters
					{
						where_clause=where_clause.concat(where.get(i).getOperators()+" ");
						where_clause=where_clause.concat(where.get(i).getFiltervalue()+" ");
					}
					
					if(i != (where.size()-1))
					{
						where_clause=where_clause.concat(where.get(i).getConnectors()+" ");
					}

				} //end for
				
				where_clause=where_clause.concat(";");
				sqlQuery = sqlQuery.concat(where_clause);
				
			} //end if
		}
		return sqlQuery;
	}

	private String generateDisplayCols(ArrayList<Node> selectedTablesNodes, String[] finalCols) {

		String cond="";

		for(int i=0;i<finalCols.length;i++){
			cond+=finalCols[i]+" ,";
		}
		
		return cond;
	}

	public String multipleTableResultsNoWhere(String[] finalCols, String[] finalTables) {
		
		ArrayList<Node> path=(ArrayList<Node>)ActionContext.getContext().getSession().get("finalJoinPath");
		
		if(path!=null){
			String displayCols="";
			ArrayList<Node> selectedTablesNodes=(ArrayList<Node>)ActionContext.getContext().getSession().get("selectedNodesList");	
			
			displayCols=generateDisplayCols(selectedTablesNodes,finalCols);
			displayCols=displayCols.substring(0, displayCols.length()-2);
			
			sqlQuery+= displayCols + " FROM ";
			
			for(int i=0;i<path.size();i++)
			{
				if(i<path.size()-1)
					sqlQuery+=path.get(i).getTblObj().getName()+", ";
				else
					sqlQuery+=path.get(i).getTblObj().getName();
			}
			
			sqlQuery+=" WHERE ";
			
			String joins="";
			
			for(int i=0;i<path.size();i++)
			{
				//appending table name
				if(i<path.size()-1)
				{
					joins+=path.get(i).getTblObj().getName()+".";
					joins+=path.get(i+1).getForeignKey()+" = ";   //form A.a = 
					joins+=path.get(i+1).getTblObj().getName()+".";
					joins+=path.get(i+1).getPrimaryKey() + " AND ";  //forms A.a = B.a
				}
			}
			
			joins= joins.substring(0, joins.length()-5);
			
			sqlQuery+=" "+joins+";";
			
		}
		else // for association
		{
			String displayCols="";
			System.out.println("inside multiple no where");
			ArrayList<Node> selectedTablesNodes=(ArrayList<Node>)ActionContext.getContext().getSession().get("selectedNodesList");	
			ArrayList<Node> pathAssociation=(ArrayList<Node>)ActionContext.getContext().getSession().get("finalJoinPathAssociation");
			displayCols=generateDisplayCols(selectedTablesNodes,finalCols);
			System.out.println("*************"+displayCols);
			displayCols=displayCols.substring(0, displayCols.length()-2);
			System.out.println("************* after"+displayCols);
			sqlQuery+=displayCols + " FROM ";
			for(int i=0;i<pathAssociation.size();i++)
			{
				if(i<pathAssociation.size()-1)
					sqlQuery+=pathAssociation.get(i).getTblObj().getName()+", ";
				else
					sqlQuery+=pathAssociation.get(i).getTblObj().getName();
			}
			sqlQuery+=" WHERE ";
			System.out.println("query till from "+sqlQuery);
			String joins="";


			//we have to join threee tables alwayz so 2 join conditions will be there
			//Intermediate node(enrollment) does not have primary or foreign key at all
			//first join first and middle table i.e enrollment and course(for example)
			joins+=pathAssociation.get(0).getTblObj().getName()+".";
			joins+=pathAssociation.get(0).getForeignKey()+" = ";   //form A.a = 
			joins+=pathAssociation.get(1).getTblObj().getName()+".";
			joins+=pathAssociation.get(0).getPrimaryKey() + " AND ";  //forms A.a = B.a

			//then join middle table and last one: enrollment and student(for example)
			joins+=pathAssociation.get(pathAssociation.size()-1).getTblObj().getName()+".";
			joins+=pathAssociation.get(pathAssociation.size()-1).getForeignKey()+" = ";   //form A.a = 
			joins+=pathAssociation.get(1).getTblObj().getName()+".";
			joins+=pathAssociation.get(pathAssociation.size()-1).getPrimaryKey();  //forms A.a = B.a

			System.out.println("Join query = " + joins);
			sqlQuery+=" "+joins+";";
			System.out.println("Final Query association > "+sqlQuery);
		}

		return sqlQuery;
	}



}
